﻿-- =============================================
-- Author:		Jon Stocksdale, based on Taylor Chase's ViewSuperRelationships
-- Create date: June 24, 2016
-- Description:	Return all Relationship Names (initial plus all super relationships above this one)
-- This is helpful for finding all relationships (super and regular) that would apply to a particular relationship. 
-- =============================================
CREATE FUNCTION [dbo].[GetRegularAndSuperRelationshipNames] 
(
	-- Just the ShortRelatioshipName
	@OrigRelationshipName nvarchar(50)
)
RETURNS 
@ReturnTable TABLE 
(
	ShortRelationshipName nvarchar(50),
	OrigRelationshipName nvarchar(50)
)
AS
BEGIN
	DECLARE @SuperRelationship nvarchar(50)
	SET @SuperRelationship = @OrigRelationshipName
		-- Loading up the original name so that this is a complete list for joins.

	WHILE @SuperRelationship IS NOT NULL BEGIN
		INSERT INTO @ReturnTable(ShortRelationshipName, OrigRelationshipName)
			VALUES(@SuperRelationship, @OrigRelationshipName)
		SELECT @SuperRelationship = SuperRelationship FROM DefinedEntityRelationships
			WHERE ShortRelationshipName = @SuperRelationship AND SuperRelationship IS NOT NULL
		IF @@ROWCOUNT = 0 SET @SuperRelationship = NULL
	END
	RETURN 
END